package com.icedata.sika.database

import java.sql.Connection
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.util.*
import kotlin.reflect.KClass
import kotlin.reflect.KMutableProperty1
import kotlin.reflect.KProperty1
import kotlin.reflect.full.createInstance
import kotlin.reflect.full.memberProperties
import kotlin.reflect.jvm.jvmName


/**
 * 数据库操作器,用于保存数据库连接与数据操作器
 */
class DatabaseOperator constructor(private val connection: Connection) : Connection by connection {

    /**
     * 保存的数据操作器
     */
    val preparedStatements: MutableMap<KClass<*>, DataOperator<*>> = mutableMapOf()

    /**
     * 根据泛型获取数据操作器
     */
    inline fun <reified T : Any> getDataOperator(): DataOperator<T> {
        return preparedStatements[T::class]?.let { it as DataOperator<T> } ?: DataOperator<T>(
            T::class, this
        ).apply { preparedStatements[T::class] = this }
    }

    /**
     * 关闭操作器
     */
    fun closeOperator() {
        preparedStatements.forEach { _, operator ->
            operator.insertPreparedStatement.close()
        }
        connection.close()
    }

}

/**
 * 数据操作器，用于操作数据库数据的保存与读取
 */
class DataOperator<T : Any>(val clazz: KClass<T>, val connection: Connection) {

    /**
     * 表名
     */
    private lateinit var tableName: String

    /**
     * 主键名
     */
    private lateinit var primaryKey: String

    /**
     * 数据库名
     */
    private lateinit var databaseName: String

    /**
     * 插入数据用的PreparedStatement
     */
    val insertPreparedStatement: PreparedStatement by lazy {
        connection.prepareStatement(
            "INSERT INTO ${if (databaseName != "") "$databaseName." else ""}$tableName ($columnsName) VALUES ($comp)",
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY
        )
    }

    /**
     * 用于读取数据的PreparedStatement
     */
    val selectPreparedStatement: PreparedStatement by lazy {
        connection.prepareStatement(
            "SELECT $columnsName FROM  ${if (databaseName != "") "$databaseName." else ""}$tableName WHERE $primaryKey=?",
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY
        )
    }

    val copyPreparedStatement: PreparedStatement by lazy {
        connection.prepareStatement(
            "SELECT $columnsName FROM ${if (databaseName != "") "$databaseName." else ""}$tableName",
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY
        )
    }

    val updatePreparedStatement: PreparedStatement by lazy {
        var sql = "UPDATE ${if (databaseName != "") "$databaseName." else ""} $tableName SET "
        columns.forEach { (name, _) ->
            sql += "$name = ?, "
        }
        sql = sql.substring(0, sql.length - 2)
        sql += " WHERE $primaryKey = ?"
        connection.prepareStatement(
            sql,
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY
        )
    }

    val deletePreparedStatement: PreparedStatement by lazy {
        connection.prepareStatement(
            "DELETE ${if (databaseName != "") "$databaseName." else ""}$tableName WHERE = ?",
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY
        )
    }

    /**
     * 列名
     */
    private lateinit var columnsName: String

    /**
     * 占位符
     */
    private lateinit var comp: String

    /**
     * 列操作
     */
    val columns: MutableMap<String, KProperty1<T, *>> = mutableMapOf()

    init {
        clazz.annotations.find { it is Table }?.let { table ->
            with(table as Table) {
                tableName = name
                databaseName = database
                this@DataOperator.primaryKey = primaryKey
            }
        }

        clazz.memberProperties.forEach { kProperty ->
            kProperty.annotations.find { it is Column }?.let {
                columns[(it as Column).name] = kProperty
            }
        }

        val columnsName = StringBuffer()
        val comp = StringBuffer()

        if (columns.isNotEmpty()) {
            columns.forEach { (k, v) ->
                columnsName.append(k).append(",")
                comp.append("?,")
            }
            columnsName.delete(columnsName.length - 1, columnsName.length)
            comp.delete(comp.length - 1, comp.length)
        }

        this.columnsName = columnsName.toString()
        this.comp = comp.toString()
    }

    /**
     * 向数据库插入一条数据
     */
    fun insert(data: T) {
        columns.onEachIndexed { index, entry ->
            insertPreparedStatement.setObject(index + 1, entry.value.get(data))
        }
        insertPreparedStatement.execute()
    }

    /**
     * 从数据库读取一条数据
     */
    fun select(primaryKey: Any): T {
        selectPreparedStatement.setObject(1, primaryKey)
        selectPreparedStatement.executeQuery()!!.let { result ->
            if (result.next()) {
                return clazz.createInstance().apply {
                    columns.onEachIndexed { index, entry ->
                        result.getObject(index + 1)?.let {
                            (entry.value as KMutableProperty1<T, Any>).set(this, it)
                        }
                    }
                }
            } else throw DataNotFoundException(clazz.jvmName)
        }
    }

    /**
     * 复制整个数据库
     */
    fun copy(): LinkedList<T> {
        val list = LinkedList<T>()
        copyPreparedStatement.executeQuery()!!.let { result ->
            while (result.next())
                list.add(clazz.createInstance().apply {
                    columns.onEachIndexed { index, entry ->
                        result.getObject(index + 1)?.let {
                            (entry.value as KMutableProperty1<T, Any>).set(this, it)
                        }
                    }
                })
        }
        return list
    }

    /**
     * 更新数据库
     */
    fun update(data: T) {
        columns.onEachIndexed { index, entry ->
            updatePreparedStatement.setObject(index + 1, entry.value.get(data))
        }
        updatePreparedStatement.setObject(columns.size + 1, columns[primaryKey]!!.get(data))
        updatePreparedStatement.execute()
    }

    /**
     * 删除数据库
     */
    fun delete(primaryKey: Any) {
        deletePreparedStatement.setObject(1, primaryKey)
        deletePreparedStatement.execute()
    }

}

class DataNotFoundException(className: String) : Exception(className)